
# выгружаем необходимые библиотеки
import pandas as pd
import datetime as dt
import plotly.express as px
import numpy as np
from scipy import stats as st
import plotly.graph_objects as go
import seaborn as sns
import matplotlib.pyplot as plt


try: 
    telecom_clients = pd.read_csv('telecom_clients.csv')
    telecom_dataset = pd.read_csv('telecom_dataset.csv')
except:
    # удалено по правилам ЯП

td = telecom_dataset.copy()


telecom_dataset.info()

<class 'pandas.core.frame.DataFrame'>
RangeIndex: 53902 entries, 0 to 53901
Data columns (total 9 columns):
 #   Column               Non-Null Count  Dtype  
---  ------               --------------  -----  
 0   user_id              53902 non-null  int64  
 1   date                 53902 non-null  object 
 2   direction            53902 non-null  object 
 3   internal             53785 non-null  object 
 4   operator_id          45730 non-null  float64
 5   is_missed_call       53902 non-null  bool   
 6   calls_count          53902 non-null  int64  
 7   call_duration        53902 non-null  int64  
 8   total_call_duration  53902 non-null  int64  
dtypes: bool(1), float64(1), int64(4), object(3)
memory usage: 3.3+ MB


telecom_dataset.isna().sum()

user_id                   0
date                      0
direction                 0
internal                117
operator_id            8172
is_missed_call            0
calls_count               0
call_duration             0
total_call_duration       0
dtype: int64


telecom_dataset.isna().mean()

user_id                0.000000
date                   0.000000
direction              0.000000
internal               0.002171
operator_id            0.151608
is_missed_call         0.000000
calls_count            0.000000
call_duration          0.000000
total_call_duration    0.000000
dtype: float64


telecom_dataset.duplicated().sum()

4900


telecom_dataset.head()


telecom_dataset['user_id'].nunique()

307


row_before = telecom_dataset.shape[0]


print(f'''Данные представлены с {telecom_dataset.date.min()} по {
telecom_dataset.date.max()} ''')

Данные представлены с 2019-08-02 00:00:00+03:00 по 2019-11-28 00:00:00+03:00


telecom_clients.info()

<class 'pandas.core.frame.DataFrame'>
RangeIndex: 732 entries, 0 to 731
Data columns (total 3 columns):
 #   Column       Non-Null Count  Dtype 
---  ------       --------------  ----- 
 0   user_id      732 non-null    int64 
 1   tariff_plan  732 non-null    object
 2   date_start   732 non-null    object
dtypes: int64(1), object(2)
memory usage: 17.3+ KB


telecom_clients.head()


telecom_clients.isna().sum()

user_id        0
tariff_plan    0
date_start     0
dtype: int64


print(f'''Данные представлены с {telecom_clients.date_start.min()} по {
telecom_clients.date_start.max()} ''')

Данные представлены с 2019-08-01 по 2019-10-31


telecom_clients.tariff_plan.value_counts()

C    395
B    261
A     76
Name: tariff_plan, dtype: int64


telecom_clients.user_id.nunique()

732


telecom_clients.isna().sum()

user_id        0
tariff_plan    0
date_start     0
dtype: int64


telecom_clients.duplicated().sum()

0


telecom_clients['user_id'].nunique()

732


telecom_dataset[telecom_dataset['operator_id'].isna()]['is_missed_call'].value_counts()

True     8050
False     122
Name: is_missed_call, dtype: int64


telecom_dataset[telecom_dataset['operator_id'].isna() & (telecom_dataset['is_missed_call'] == True)] = telecom_dataset[
    (telecom_dataset['operator_id'].isna()) & (telecom_dataset['is_missed_call'] == True)].fillna('0')


telecom_dataset[telecom_dataset['operator_id'].isna() & (telecom_dataset['is_missed_call'] == False)] = telecom_dataset[
    (telecom_dataset['operator_id'].isna()) & (telecom_dataset['is_missed_call'] == False)].dropna()


(telecom_dataset
 .query('internal != internal')
 .groupby(['is_missed_call','direction'])
 .agg({'user_id':'nunique','calls_count':'sum','total_call_duration':'sum'})
 .assign(calls_share = lambda x: x['calls_count'] / telecom_dataset.query('internal != internal').calls_count.sum(),
         total_calls_share = lambda x: x['calls_count'] / telecom_dataset.calls_count.sum())
)


telecom_dataset.shape[0]

53902


telecom_dataset = telecom_dataset.dropna(subset=['internal'])


telecom_dataset.shape[0]

53720


telecom_dataset.isna().sum()

user_id                0
date                   0
direction              0
internal               0
operator_id            0
is_missed_call         0
calls_count            0
call_duration          0
total_call_duration    0
dtype: int64


# количество клиентов и операторов до фильтрации:

td[['user_id','operator_id']].nunique()

user_id         307
operator_id    1092
dtype: int64


# количество клиентов и операторов после фильтрации:
telecom_dataset[['user_id','operator_id']].nunique()

user_id         307
operator_id    1093
dtype: int64


# длительность исходящих до и после:
td.query('direction == "out"').total_call_duration.sum() - telecom_dataset.query('direction == "out"').total_call_duration.sum()

190573.0


telecom_dataset.date = pd.to_datetime(telecom_dataset['date']).dt.date.astype('datetime64[ns]')


telecom_clients['date_start'] = pd.to_datetime(telecom_clients['date_start']).dt.date.astype('datetime64[ns]')


telecom_dataset['operator_id'] = telecom_dataset['operator_id'].astype('int64')


telecom_dataset['user_id'] = telecom_dataset['user_id'].astype('int64')


telecom_dataset.dtypes

user_id                         int64
date                   datetime64[ns]
direction                      object
internal                       object
operator_id                     int64
is_missed_call                 object
calls_count                   float64
call_duration                 float64
total_call_duration           float64
dtype: object


telecom_clients.dtypes

user_id                 int64
tariff_plan            object
date_start     datetime64[ns]
dtype: object


telecom_dataset.shape[0]

53720


telecom_dataset[
    ['user_id', 'date',  'direction', 'internal', 'operator_id', 'is_missed_call']
].duplicated().sum()

4886


telecom_dataset = telecom_dataset.drop_duplicates(ignore_index=True)


telecom_dataset.shape[0]

48834


telecom_dataset.head()


telecom_dataset[
    ['user_id', 'date',  'direction', 'internal', 'operator_id', 'is_missed_call']
].duplicated().sum()

0


group_td = telecom_dataset.pivot_table( 
    index = ['user_id', 'date', 'operator_id'], 
    values = ['call_duration', 'total_call_duration', 'calls_count'], 
    aggfunc = 'sum').reset_index()
group_td['total_call_duration_h']  = group_td['total_call_duration']/60/60
group_td['call_duration_h']  = group_td['call_duration']/60/60
group_td = group_td.query('operator_id != 0')
group_td[group_td['total_call_duration_h'] >24]


more_24 = list(group_td[group_td['total_call_duration_h'] >24]['user_id'].unique())
more_24

[166582]


telecom_dataset = telecom_dataset[~telecom_dataset['user_id'].isin(more_24)]


telecom_clients = telecom_clients[~telecom_clients['user_id'].isin(more_24)]


group_td = group_td[group_td['user_id']!=166582]


telecom_dataset['user_id'].nunique()

306


telecom_clients[['user_id', 'tariff_plan']].duplicated().sum()

0


telecom_clients[['user_id', 'date_start']].duplicated().sum()

0


fig = px.box(group_td, y="calls_count")
fig.update_layout(title='Количество звонков',
                 yaxis_title='Количество звонков')
fig.show()


telecom_dataset[telecom_dataset['calls_count'] > 1000]['is_missed_call'].value_counts()

True    24
Name: is_missed_call, dtype: int64


group_td[group_td['calls_count'] > 2000]


telecom_dataset[(telecom_dataset['operator_id'] == 929428) 
                & (telecom_dataset['date'] == '2019-11-11')]


fig = px.box(group_td, y="total_call_duration_h")
fig.update_layout(title='Длительность всех звонков с учётом времени ожидания.',
                 yaxis_title='Длительность, час')
fig.show()


more_12  = group_td[group_td['total_call_duration_h'] > 12].sort_values(by='total_call_duration_h').reset_index()
more_12


telecom_dataset.info()

<class 'pandas.core.frame.DataFrame'>
Int64Index: 48114 entries, 0 to 48833
Data columns (total 9 columns):
 #   Column               Non-Null Count  Dtype         
---  ------               --------------  -----         
 0   user_id              48114 non-null  int64         
 1   date                 48114 non-null  datetime64[ns]
 2   direction            48114 non-null  object        
 3   internal             48114 non-null  object        
 4   operator_id          48114 non-null  int64         
 5   is_missed_call       48114 non-null  object        
 6   calls_count          48114 non-null  float64       
 7   call_duration        48114 non-null  float64       
 8   total_call_duration  48114 non-null  float64       
dtypes: datetime64[ns](1), float64(3), int64(2), object(3)
memory usage: 3.7+ MB


without_missed = telecom_dataset.loc[~((telecom_dataset['direction'] == "in") 
                                    & (telecom_dataset['internal'] == False) 
                                    & (telecom_dataset['is_missed_call'] == True))]


# выделяем user_id всех клиентов
user_id_all = list(telecom_dataset['user_id'].unique())
len(user_id_all)

306


without_missed['user_id'].nunique()

290


error_user_id = list(set(user_id_all) - set(without_missed['user_id']))
len(error_user_id)

16


telecom_dataset
telecom_dataset['month'] = telecom_dataset['date'].dt.month
telecom_dataset.head()


group_df = telecom_dataset.pivot_table(
    index = ['user_id', 'month'], 
    values=[ "calls_count", 'call_duration', "total_call_duration"],
               aggfunc={ 
                        "calls_count": np.sum,
                        "call_duration": np.sum,
                        "total_call_duration": np.sum}).reset_index()
group_df['total_call_duration_m'] = group_df['total_call_duration'].map(lambda x: np.ceil(x/60))


group_df.head()


fig = px.box(group_df, x="month", y=["total_call_duration_m"])
fig.update_layout(title='Длительность всех звонков с учётом времени ожидания.',
                 yaxis_title='Длительность, мин', 
                  xaxis_title='Месяц')
fig.show()


fig = px.box(group_df, x="month", y=["total_call_duration_m"])
fig.update_layout(title='Длительность всех звонков с учётом времени ожидания.',
                 yaxis_title='Длительность, мин', 
                  xaxis_title='Месяц')
fig.update_yaxes(range=[0, 2000])
fig.show()


fig = px.box(group_df, x="month", y=["calls_count"])
fig.update_layout(title='Количество звонков у клиентов по месяцам',
                 yaxis_title='Количество звонков', 
                  xaxis_title='Месяц')

fig.show()


fig = px.box(group_df, x="month", y=["calls_count"])
fig.update_layout(title='Количество звонков у клиентов по месяцам',
                 yaxis_title='Количество звонков', 
                  xaxis_title='Месяц')
fig.update_yaxes(range=[0, 2000])
fig.show()


first_month = 9
second_month = 10 
third_month = 11


condition = []
for i in range(first_month, third_month+1):
    condition.append(group_df[group_df['month'] == i]['total_call_duration_m'].quantile(.50))
condition

[60.0, 59.5, 102.5]


# формируем список id активных пользователей
active_user_id_1 = list(group_df[((group_df['total_call_duration_m'] >= condition[0]) 
                                  & (group_df['month'] == first_month))]['user_id'].unique())
active_user_id_2 = list(group_df[((group_df['total_call_duration_m'] >= condition[1]) 
                                  & (group_df['month'] == second_month))]['user_id'].unique())
active_user_id_3 = list(group_df[((group_df['total_call_duration_m'] >= condition[2]) 
                                  & (group_df['month'] == third_month))]['user_id'].unique())
active_user_id = list(set(active_user_id_1) & set(active_user_id_2) & set(active_user_id_3))
len(active_user_id)

57


#определяем пассивных пользователей
passive_user_id = list(set(user_id_all) - set(active_user_id))
len(passive_user_id)

249


upper_bound = group_df[group_df['user_id'].isin(active_user_id)]['total_call_duration'].quantile(q=0.975)
duration_active = group_df[(
    group_df['user_id'].isin(active_user_id) & (group_df['total_call_duration'] <=upper_bound))]['total_call_duration']
duration_active.mean()

68544.22959183673


upper_bound_p = group_df[group_df['user_id'].isin(passive_user_id)]['total_call_duration'].quantile(q=0.975)
duration_passive = group_df[
    (group_df['user_id'].isin(passive_user_id) & (group_df['total_call_duration'] <=upper_bound_p))]['total_call_duration']
duration_passive.mean()

16722.484265734267


alpha = 0.05 

# equal_var = False, n к у нас выборки разного размера
results = st.ttest_ind(duration_passive, duration_active, equal_var = False) 


print('p-значение:', results.pvalue)

if results.pvalue < alpha:
    print('Отвергаем нулевую гипотезу')
else:
    print('Не получилось отвергнуть нулевую гипотезу')

p-значение: 2.8205650014235025e-14
Отвергаем нулевую гипотезу


duration_active.mean()/duration_passive.mean()

4.0989262422144686


tarif_data = telecom_dataset.merge(telecom_clients, on='user_id', how = 'inner')
tarif_data.head(10)


billing_data = tarif_data[['user_id', 'month', 'tariff_plan']]
billing_data = billing_data.drop_duplicates().reset_index(drop=True)
billing_data.tail()


# добавляем количество операторов, убирая значение оператор 0
billing_data = billing_data.merge(tarif_data.query('operator_id != 0').pivot_table(index = ['user_id', 'month'],
                      values = ['operator_id'], 
                      aggfunc={"operator_id": 'nunique'}).reset_index(), on=['user_id', 'month'], how = 'outer')
billing_data = billing_data.rename(columns = {'operator_id':'operator_count'})
billing_data.head()


# считаем длительность всех входящтих вызовов, не выделяя внутренние и внешние
in_call = tarif_data.query('direction == "in"').pivot_table(
    index = ['user_id', 'date', 'month', 'operator_id'],
                      values = ['call_duration'], 
                      aggfunc={"call_duration": 'sum'}).reset_index()
in_call['call_duration'] = in_call['call_duration'].map(lambda x: np.ceil(x/60))
in_call = in_call.rename(columns = {'call_duration':'in_call_m'})


in_call = in_call.pivot_table(index = ['user_id', 'month'],
                      values = ['in_call_m'], 
                      aggfunc={"in_call_m": 'sum'}).reset_index()
in_call


out_internal_call = tarif_data.query('direction == "out" and internal == True').pivot_table(
    index = ['user_id', 'date', 'month', 'operator_id'],
                      values = ['total_call_duration'], 
                      aggfunc={"total_call_duration": 'sum'}).reset_index()
out_internal_call['total_call_duration'] = out_internal_call['total_call_duration'].map(lambda x: np.ceil(x/60))
out_internal_call = out_internal_call.rename(columns = {'total_call_duration':'out_internal_call_m'})


out_internal_call = out_internal_call.pivot_table(index = ['user_id', 'month'],
                      values = ['out_internal_call_m'], 
                      aggfunc={"out_internal_call_m": 'sum'}).reset_index()

out_internal_call.head()


# считаем количество минут для каждого оператора
out_no_internal_call = tarif_data.query('direction == "out" and internal == False').pivot_table(
    index = ['user_id', 'date', 'month', 'operator_id'],
                      values = ['total_call_duration'], 
                      aggfunc={"total_call_duration": 'sum'}).reset_index()
out_no_internal_call['total_call_duration'] = out_no_internal_call['total_call_duration'].map(lambda x: np.ceil(x/60))
out_no_internal_call = out_no_internal_call.rename(columns = {'total_call_duration':'out_no_internal_call_m'})
# производим группировку по клиентам
out_no_internal_call = out_no_internal_call.pivot_table(index = ['user_id', 'month'],
                      values = ['out_no_internal_call_m'], 
                      aggfunc={"out_no_internal_call_m": 'sum'}).reset_index()
out_no_internal_call.head()


billing_data.head()


# получили итоговую таблицу с минутами
billing_data = billing_data.merge(out_no_internal_call, on=['user_id', 'month'], how = 'outer')
billing_data = billing_data.merge(out_internal_call, on=['user_id', 'month'], how = 'outer')
billing_data = billing_data.merge(in_call, on=['user_id', 'month'], how = 'outer')
billing_data = billing_data.fillna(0)
billing_data.head()


tariffs = pd.DataFrame({
    'tariff_name':['A', 'B', 'C'],
    'month_fee':[4000, 2000, 1000],
    'inside_calls':[0.1, 0.15, 0.3],
    'outside_calls':[0.3, 0.5, 0.7],
    'operator':[50, 150, 300],
    'internal_call_limit':[2000, 500, 0]
})
tariffs


# Проведем расчеты затрат по всем тарифам:

def monthly_revenue_best(row):
    
    # Добавление ежемесячной абонентской платы по всем тарифам:
    result = []
    for i in list(tariffs['tariff_name'].unique()):
        tariff_name = str(i)
        
        # расчет операторов:
        total = row['operator_count'] * int(tariffs[tariffs['tariff_name'] == tariff_name]['operator'])
        
        # добавление ежемесячной платы:
        total += int(tariffs[tariffs['tariff_name'] == tariff_name]['month_fee'])
        
        # добавление платы за исходящие внешние:                 
        total += row['out_no_internal_call_m'] * float(tariffs[tariffs['tariff_name'] == tariff_name]['outside_calls'])
                 
        # добавление платы за исходящие внутренние:            
        if row['out_internal_call_m'] > float(tariffs[tariffs['tariff_name'] == tariff_name]['internal_call_limit']):
            total += (row['out_internal_call_m'] - float(tariffs[tariffs['tariff_name'] == tariff_name]['internal_call_limit'])) \
                         * float(tariffs[tariffs['tariff_name'] == tariff_name]['inside_calls'])
        
        result += [[tariff_name, total]]
        
    # на основе расчетов выше созодадим датафрейм с тарифами:                             
    tariff_costs = pd.DataFrame(result, columns = ['tariff_name', 'cost'])
                     
    # выбираем оптимальный тариф:   
    best_tariff_name = tariff_costs.nsmallest(1, 'cost')['tariff_name'].values[0]
    best_cost = tariff_costs.nsmallest(1, 'cost')['cost'].values[0]
            
    # определим затраты по действующему тарифу:   
    current_cost = tariff_costs[tariff_costs['tariff_name'] == row['tariff_plan']]['cost'].values[0]
    
    
            
    # добавляем столбцы в исходный датафрейм:                    
    row['pay'] = current_cost          
    row['after_optimisation'] = best_cost
    row['optimal_tariff'] = best_tariff_name
    
    # определяем нужно ли менять тариф 
    if row['tariff_plan'] == row['optimal_tariff']:
        val = False
    else :
         val = True
    row['change'] = val
    
    
    return row    
                         
billing_data = billing_data.apply(monthly_revenue_best, axis = 1)


billing_data.head()


optimisation = billing_data.query('month !=8').pivot_table(
    index = ['user_id'], values = ['pay', 'after_optimisation', 'change', 'optimal_tariff'], 
    aggfunc={"pay": 'sum',
            "after_optimisation": 'sum',
            'change': 'sum',
            'optimal_tariff': 'nunique'}).reset_index()
optimisation.head()


# список пользователей, которые нужно предложить сменить тариф? как активных, так и пассивных
optim = list(optimisation.query('change == 3 and optimal_tariff ==1')['user_id'])
optim

[166406,
 166407,
 166428,
 166495,
 166503,
 166520,
 166533,
 166536,
 166541,
 166548,
 166609,
 166658,
 166660,
 166678,
 166697,
 166711,
 166713,
 166725,
 166732,
 166774,
 166800,
 166813,
 166839,
 166843,
 166879,
 166884,
 166896,
 166899,
 166916,
 166971,
 166975,
 166983,
 167002,
 167004,
 167061,
 167071,
 167077,
 167078,
 167082,
 167109,
 167110,
 167150,
 167158,
 167172,
 167175,
 167185,
 167250,
 167263,
 167264,
 167265,
 167275,
 167338,
 167402,
 167436,
 167451,
 167475,
 167479,
 167487,
 167543,
 167621,
 167634,
 167654,
 167716]


# список активных клиентов, которым нужно предложить изменить тариф
optim_active = list(set(optim) & set(active_user_id))
len(optim_active)

26


optim_active

[166658,
 166916,
 167172,
 166406,
 166407,
 166536,
 167175,
 166541,
 166800,
 166678,
 167071,
 167082,
 167479,
 167487,
 167109,
 166725,
 166983,
 167004,
 166495,
 167264,
 166879,
 166503,
 167150,
 166896,
 166899,
 166520]


billing_data.loc[~billing_data['user_id'].isin(optim_active),  'after_optimisation'] = billing_data['pay']
billing_data.loc[~billing_data['user_id'].isin(optim_active),  'optimal_tariff'] = billing_data['tariff_plan']
billing_data.loc[~billing_data['user_id'].isin(active_user_id),  'status'] = 'пассивный'
billing_data.loc[billing_data['user_id'].isin(active_user_id),  'status'] = 'активный'
billing_data.head()


income_before_optimisation = billing_data.loc[((billing_data['month'] == first_month) 
| (billing_data['month'] == second_month) 
| (billing_data['month'] == third_month)), 'pay' ]

income_after_optimisation = billing_data.loc[((billing_data['month'] == first_month) 
| (billing_data['month'] == second_month) 
| (billing_data['month'] == third_month)), 'after_optimisation' ]


print(f'''Для исследования был выбран период с {first_month} по {third_month} месяц. 
Выручка до оптимизации тарифов за данный период составила : {income_before_optimisation.sum()}
Выручка после оптимизации тарифов за данный период составила: {income_after_optimisation.sum()}
Сокращение выручки составило: {round(income_before_optimisation.sum() - income_after_optimisation.sum(), 2)} рублей или {
round(((income_before_optimisation.sum() - income_after_optimisation.sum())/income_before_optimisation.sum())*100, 2)}% ''')

Для исследования был выбран период с 9 по 11 месяц. 
Выручка до оптимизации тарифов за данный период составила : 1788925.4499999997
Выручка после оптимизации тарифов за данный период составила: 1722596.75
Сокращение выручки составило: 66328.7 рублей или 3.71%


alpha = 0.05  

results = st.mannwhitneyu(list(income_before_optimisation),  list(income_after_optimisation),  True, 'greater')

print('p-значение: ', results.pvalue)

if results.pvalue < alpha:
    print('Отвергаем нулевую гипотезу: разница статистически значима')
else:
    print(
        'Не получилось отвергнуть нулевую гипотезу, вывод о различии сделать нельзя'
    )

p-значение:  0.0807109922236971
Не получилось отвергнуть нулевую гипотезу, вывод о различии сделать нельзя


arpu = billing_data.pivot_table(index= 'month', values = ['pay', 'user_id' ] , aggfunc = ({'user_id': "nunique",'pay': 'sum' }))
arpu['arpu'] = round(arpu['pay']/arpu['user_id'], 2)
arpu


month = arpu.index

fig = go.Figure(
    data=go.Bar(
        x=month,
        y=arpu['arpu'].values,
        name="ARPU",
        
    )
)

fig.add_trace(
    go.Scatter(
        x=month,
        y=arpu['user_id'].values,
        yaxis="y2",
        name="Количество клиентов"
    )
)


fig.update_layout(
    title="Средняя выручка по месяцам в расчёте на одного клиента",
    legend=dict(orientation="h"),
    yaxis=dict(
        title=dict(text="ARPU, руб."),
        side="left",
        range=[0, 3000],
    ),
    yaxis2=dict(
        title=dict(text="Количество клиентов"),
        side="right",
        range=[0, 300],
        overlaying="y",
        
    ),
   
)


fig.show()


billing_data


ARPM = billing_data.pivot_table(index= 'month', 
                                values = ['pay', 'in_call_m', 'out_no_internal_call_m', 'out_internal_call_m'] , 
                                aggfunc =  'sum')
ARPM['total_m'] = ARPM['in_call_m']+ARPM['out_no_internal_call_m']+ARPM['out_internal_call_m']

ARPM['ARPM'] = round(ARPM['pay']/(ARPM['total_m']), 2)

ARPM


month = ARPM.index

fig = go.Figure(
    data=go.Bar(
        x=month,
        y=ARPM['ARPM'].values,
        name="ARPM",
        
    )
)

fig.add_trace(
    go.Scatter(
        x=month,
        y=ARPM['total_m'],
        yaxis="y2",
        name="Количество минут"
    )
)


fig.update_layout(
    title="Средняя выручка на одну минуту разговора",
    legend=dict(orientation="h"),
    yaxis=dict(
        title=dict(text="ARPM, руб."),
        side="left",
        range=[0, 10],
    ),
    yaxis2=dict(
        title=dict(text="Количество минут"),
        side="right",
        range=[16000, 350000],
        overlaying="y",
    
    ),
   
)

fig.show()


fig = px.pie(billing_data[['user_id', 'status']].drop_duplicates().status.value_counts(), values='status', 
             names=billing_data[['user_id', 'status']].drop_duplicates().status.value_counts().index, 
             title='Распределение клиентов')
fig.show()


pivot_out_no_internal = billing_data.pivot_table(
    index= ['tariff_plan', 'status', 'month'], values ='out_no_internal_call_m', aggfunc='sum' ).reset_index()
pivot_out_no_internal


#посмотрим в разрезе тарифов распределение времени
fig, ax = plt.subplots(1, 1, figsize=(17, 6))
sns.boxplot(data=billing_data.sort_values(by = ['month','tariff_plan']), 
            x="month", y="out_no_internal_call_m", hue="status" )

plt.title('Распределение времени, истраченного клиентами на исходящие внешние вызовы')
plt.ylabel('Количество минут')
plt.xlabel('Месяц')
ax.set_ylim([0, 4000])
plt.show()


fig = px.histogram(pivot_out_no_internal, 
             y= 'out_no_internal_call_m', 
             x = 'month',
             barmode = 'group',
             color = 'status', height=400, color_discrete_sequence= px.colors.qualitative.Pastel, 
             category_orders={'status': pivot_out_no_internal['status']})

fig.update_layout(title='Медианное значение количества минут, истраченное клиентами на исходящие внешние вызовы',
                   xaxis_title='Месяц',
                   yaxis_title='Количество минут')

fig.show()


pivot_out_internal = billing_data.pivot_table(
    index= ['tariff_plan', 'status', 'month'], values ='out_internal_call_m', aggfunc='mean' ).reset_index()


#посмотрим в разрезе тарифов распределение времени
fig, ax = plt.subplots(1, 1, figsize=(17, 6))
sns.boxplot(data=billing_data.sort_values(by = ['month','tariff_plan']), 
            x="month", y="out_internal_call_m", hue="status" )

plt.title('Распределение времени, истраченного клиентами на внутренние исходящие вызовы')
plt.ylabel('Количество минут')
plt.xlabel('Месяц')
ax.set_ylim([0, 50])
plt.show()


fig = px.histogram(pivot_out_internal, 
             y= 'out_internal_call_m', 
             x = 'month',
             barmode = 'group',
             color = 'status', height=400, color_discrete_sequence= px.colors.qualitative.Pastel, 
             category_orders={'status': pivot_out_internal['status']})

fig.update_layout(title='Среднее количество минут, истраченное клиентами на внутренние исходящие вызовы',
                   xaxis_title='Месяц',
                   yaxis_title='Количество минут')

fig.show()


pivot_in = billing_data.pivot_table(
    index= ['tariff_plan', 'status', 'month'], values ='in_call_m', aggfunc='mean' ).reset_index()
pivot_in.head()


fig, ax = plt.subplots(1, 1, figsize=(10, 6))
sns.lineplot(data=pivot_in, 
            x="month", y="in_call_m", hue="tariff_plan", style='status' )

plt.title('Среднее количество минут, затраченное операторами на входящие звонки')
plt.ylabel('Количество минут')
plt.xlabel('Месяц')

plt.show()


fig = px.histogram(pivot_in, 
             y= 'in_call_m', 
             x = 'month',
             barmode = 'group',
             color = 'status', height=400, color_discrete_sequence= px.colors.qualitative.Pastel, 
             category_orders={'status': pivot_in['status']})

fig.update_layout(title='Среднее количество минут, затраченное операторами на входящие звонки',
                   xaxis_title='Месяц',
                   yaxis_title='Количество минут')

fig.show()


pivot_11 = billing_data.query('month ==11').pivot_table(
    index= ['tariff_plan', 'status'], values ='pay', aggfunc='mean' ).reset_index()
pivot_11


fig = px.histogram(pivot_11, 
             y= 'pay', 
             x = 'tariff_plan',
             barmode = 'group',
             color = 'status', height=400, color_discrete_sequence= px.colors.qualitative.Pastel, 
             category_orders={'status': pivot_11['status']})

fig.update_layout(title='Средний заработок с одного клиента в ноябре 2019',
                   xaxis_title='Тариф',
                   yaxis_title='Выручка с одного клиента')

fig.show()


pivot_11_opt = billing_data.query('month ==11').pivot_table(
    index= ['optimal_tariff', 'status'], values ='after_optimisation', aggfunc='mean' ).reset_index()
pivot_11_opt


fig = px.histogram(pivot_11_opt, 
             y= 'after_optimisation', 
             x = 'optimal_tariff',
             barmode = 'group',
             color = 'status', height=400, color_discrete_sequence= px.colors.qualitative.Pastel, 
             category_orders={'status': pivot_11_opt['status']})

fig.update_layout(title='Средний заработок с одного клиента в ноябре 2019 при отптимизации тарифов',
                   xaxis_title='Тариф',
                   yaxis_title='Выручка с одного клиента')

fig.show()

Тарифный план	Ежемесячная плата	Исходящие звонки* внутри сети**	Лимит внут. исх	Исходящие звонки*, кроме внутренних	Плата за 1 оператора
А	4 000 ₽	0.10 ₽/мин.	2000	0.3 ₽/мин.	50 ₽/оператор
В	2 000 ₽	0.15 ₽/мин.	500	0.5 ₽/мин.	150 ₽/оператор
С	1 000 ₽	0.30 ₽/мин.	0	0.7 ₽/мин.	300 ₽/оператор

	user_id	tariff_plan	date_start
0	166713	A	2019-08-15
1	166901	A	2019-08-23
2	168527	A	2019-10-29
3	167097	A	2019-09-01
4	168193	A	2019-10-16

	user_id	date	operator_id	call_duration	calls_count	total_call_duration	total_call_duration_h	call_duration_h
3167	166582	2019-08-21	885890	68400.0	849.0	87794.0	24.387222	19.000000
3185	166582	2019-08-27	885890	63741.0	1077.0	89853.0	24.959167	17.705833
3191	166582	2019-08-29	885890	68467.0	1109.0	96824.0	26.895556	19.018611
3202	166582	2019-09-02	885876	91447.0	1510.0	126416.0	35.115556	25.401944
3205	166582	2019-09-03	885876	88710.0	1897.0	132757.0	36.876944	24.641667
3208	166582	2019-09-04	885876	94055.0	1374.0	126723.0	35.200833	26.126389
3211	166582	2019-09-05	885876	111307.0	1000.0	135635.0	37.676389	30.918611
3214	166582	2019-09-06	885876	89539.0	1008.0	114881.0	31.911389	24.871944
3223	166582	2019-09-09	885876	122521.0	1700.0	160826.0	44.673889	34.033611
3226	166582	2019-09-10	885876	81237.0	1788.0	122399.0	33.999722	22.565833
3229	166582	2019-09-11	885876	115702.0	1418.0	149414.0	41.503889	32.139444
3230	166582	2019-09-11	885890	64842.0	1099.0	92460.0	25.683333	18.011667
3232	166582	2019-09-12	885876	126716.0	1104.0	153793.0	42.720278	35.198889
3233	166582	2019-09-12	885890	73071.0	1025.0	98626.0	27.396111	20.297500
3235	166582	2019-09-13	885876	99893.0	1139.0	127482.0	35.411667	27.748056
3236	166582	2019-09-13	885890	64698.0	1168.0	93791.0	26.053056	17.971667
3244	166582	2019-09-16	885876	72631.0	1369.0	103300.0	28.694444	20.175278
3247	166582	2019-09-17	885876	101163.0	2206.0	150486.0	41.801667	28.100833
3250	166582	2019-09-18	885876	121836.0	1638.0	159178.0	44.216111	33.843333
3251	166582	2019-09-18	885890	66160.0	1118.0	93031.0	25.841944	18.377778
3253	166582	2019-09-19	885876	109852.0	1252.0	137613.0	38.225833	30.514444
3254	166582	2019-09-19	885890	65188.0	1179.0	94955.0	26.376389	18.107778
3256	166582	2019-09-20	885876	116836.0	1331.0	147668.0	41.018889	32.454444
3257	166582	2019-09-20	885890	64946.0	1207.0	95081.0	26.411389	18.040556
3265	166582	2019-09-23	885876	104202.0	2186.0	153313.0	42.586944	28.945000
3266	166582	2019-09-23	885890	59430.0	1145.0	87746.0	24.373889	16.508333
3268	166582	2019-09-24	885876	62115.0	2325.0	116310.0	32.308333	17.254167
3271	166582	2019-09-25	885876	145006.0	2039.0	189989.0	52.774722	40.279444
3274	166582	2019-09-26	885876	137138.0	1603.0	172165.0	47.823611	38.093889
3277	166582	2019-09-27	885876	116527.0	1487.0	149680.0	41.577778	32.368611
3286	166582	2019-09-30	885876	94947.0	1723.0	132552.0	36.820000	26.374167
3289	166582	2019-10-01	885876	89312.0	2767.0	150203.0	41.723056	24.808889
3292	166582	2019-10-02	885876	122108.0	1570.0	155402.0	43.167222	33.918889
3296	166582	2019-10-03	885876	95104.0	484.0	105786.0	29.385000	26.417778
3300	166582	2019-10-04	885876	84057.0	449.0	94110.0	26.141667	23.349167
3320	166582	2019-10-09	885876	120476.0	1007.0	141573.0	39.325833	33.465556
3324	166582	2019-10-10	885876	90779.0	523.0	101498.0	28.193889	25.216389
3352	166582	2019-10-17	885890	69508.0	1305.0	101322.0	28.145000	19.307778
3356	166582	2019-10-18	885890	64445.0	1166.0	94070.0	26.130556	17.901389

	user_id	date	operator_id	call_duration	calls_count	total_call_duration	total_call_duration_h	call_duration_h
20427	167827	2019-10-31	929428	2644.0	2730.0	49759.0	13.821944	0.734444
20454	167827	2019-11-11	929428	9320.0	5022.0	18700.0	5.194444	2.588889

	index	user_id	date	operator_id	call_duration	calls_count	total_call_duration	total_call_duration_h	call_duration_h
0	15220	167285	2019-10-23	908640	25142.0	1602.0	43595.0	12.109722	6.983889
1	20431	167827	2019-11-01	929428	14584.0	1802.0	47098.0	13.082778	4.051111
2	15218	167285	2019-10-22	908640	26994.0	1748.0	49666.0	13.796111	7.498333
3	20427	167827	2019-10-31	929428	2644.0	2730.0	49759.0	13.821944	0.734444
4	25791	168361	2019-11-04	945278	11553.0	227.0	61611.0	17.114167	3.209167
5	20352	167827	2019-10-10	929424	41603.0	1491.0	63253.0	17.570278	11.556389

Телеком: поиск клиентов с неоптимальными тарифами¶

Описание проекта¶

Общая информация о проекте.¶

Тарифная сетка¶

Привила выставления счета¶

План исследования¶

Часть 1. Изучение общей информации и подготовка данных:¶

Получение общей информации, загрузка библиотек.¶

Изучение данных из telecom_dataset¶

Изучение telecom_clients¶

Нахождение и ликвидация пропусков.¶

Приведение данных к нужным типам¶

Нахождение и ликвидация дубликатов.¶

Дубликаты в telecom_dataset¶

Дубликаты в telecom_clients¶

Нахождение и ликвидация аномалий.¶

Поиск аномалий в calls_count¶

Поиск аномалий в total_call_duration¶

Часть 2. Исследовательская часть:¶

Определение активных клиентов.¶

Проверка гипотезы "Активные клиенты говорят больше, чем неактивные".¶

Поиск активных клиентов, использующих неэффективный тариф.¶

Расчет текущей выручки и выручки после оптимизации тарифов.¶

Проверка гипотезы "Средние затраты киентов не изменились после оптимизации".¶

Оценка деятельности компании¶

Изучение потребительского поведения неактивных и активных клиентов.¶

Выводы¶

Общие выводы¶

Рекомендации¶

Ссылка на презентацию¶

Ссылка на дашборд¶

	user_id	date	direction	internal	operator_id	is_missed_call	calls_count	call_duration	total_call_duration
0	166377	2019-08-04 00:00:00+03:00	in	False	NaN	True	2	0	4
1	166377	2019-08-05 00:00:00+03:00	out	True	880022.0	True	3	0	5
2	166377	2019-08-05 00:00:00+03:00	out	True	880020.0	True	1	0	1
3	166377	2019-08-05 00:00:00+03:00	out	True	880020.0	False	1	10	18
4	166377	2019-08-05 00:00:00+03:00	out	False	880022.0	True	3	0	25

		user_id	calls_count	total_call_duration	calls_share	total_calls_share
is_missed_call	direction
False	in	29	64.0	8166.0	0.984615	0.000072
True	in	1	1.0	136.0	0.015385	0.000001

	user_id	date	direction	internal	operator_id	is_missed_call	calls_count	call_duration	total_call_duration
0	166377	2019-08-04	in	False	0	True	2.0	0.0	4.0
1	166377	2019-08-05	out	True	880022	True	3.0	0.0	5.0
2	166377	2019-08-05	out	True	880020	True	1.0	0.0	1.0
3	166377	2019-08-05	out	True	880020	False	1.0	10.0	18.0
4	166377	2019-08-05	out	False	880022	True	3.0	0.0	25.0

	user_id	date	direction	internal	operator_id	is_missed_call	calls_count	call_duration	total_call_duration
36979	167827	2019-11-11	out	False	929428	False	205.0	9320.0	13171.0
36981	167827	2019-11-11	out	False	929428	True	4817.0	0.0	5529.0

	user_id	month	call_duration	calls_count	total_call_duration	total_call_duration_m
0	166377	8	66274.0	977.0	79999.0	1334.0
1	166377	9	87074.0	1452.0	107813.0	1797.0
2	166377	10	83758.0	1406.0	101952.0	1700.0
3	166377	11	80881.0	1314.0	98457.0	1641.0
4	166391	8	243.0	18.0	806.0	14.0

	user_id	month	tariff_plan
783	168583	11	B
784	168598	11	C
785	168601	11	C
786	168603	11	B
787	168606	11	C

	user_id	month	in_call_m
0	166377	8	27.0
1	166377	9	36.0
2	166377	10	45.0
3	166377	11	55.0
4	166391	8	5.0
...	...	...	...
774	168583	11	20.0
775	168598	11	76.0
776	168601	11	147.0
777	168603	11	0.0
778	168606	11	12.0

	user_id	month	out_no_internal_call_m
0	166377	8	1322.0
1	166377	9	1792.0
2	166377	10	1679.0
3	166377	11	1614.0
4	166405	8	859.0

	user_id	after_optimisation	change	optimal_tariff	pay
0	166377	10376.4	2	2	10492.5
1	166391	3600.0	0	1	3600.0
2	166392	4800.0	0	1	4800.0
3	166399	2600.0	0	1	2600.0
4	166405	16477.5	2	2	16913.5

	pay	user_id	arpu
month
8	187539.60	84	2232.61
9	422674.70	172	2457.41
10	681319.10	272	2504.85
11	684931.65	260	2634.35

	in_call_m	out_internal_call_m	out_no_internal_call_m	pay	total_m	ARPM
month
8	3260.0	108.0	16751.0	187539.60	20119.0	9.32
9	20087.0	1745.0	87368.0	422674.70	109200.0	3.87
10	54267.0	3578.0	201248.0	681319.10	259093.0	2.63
11	84056.0	6977.0	233721.0	684931.65	324754.0	2.11

	tariff_plan	status	month	out_no_internal_call_m
0	A	активный	8	51.0
1	A	активный	9	479.0
2	A	активный	10	2483.0
3	A	активный	11	2425.0
4	A	пассивный	8	169.0
5	A	пассивный	9	21442.0
6	A	пассивный	10	88628.0
7	A	пассивный	11	117787.0
8	B	активный	8	12454.0
9	B	активный	9	24145.0
10	B	активный	10	32564.0
11	B	активный	11	33803.0
12	B	пассивный	8	1809.0
13	B	пассивный	9	4644.0
14	B	пассивный	10	15500.0
15	B	пассивный	11	22767.0
16	C	активный	8	1327.0
17	C	активный	9	18770.0
18	C	активный	10	27028.0
19	C	активный	11	19289.0
20	C	пассивный	8	941.0
21	C	пассивный	9	17888.0
22	C	пассивный	10	35045.0
23	C	пассивный	11	37650.0

	tariff_plan	status	month	in_call_m
0	A	активный	8	8.500000
1	A	активный	9	234.428571
2	A	активный	10	667.571429
3	A	активный	11	515.571429
4	A	пассивный	8	14.250000

	tariff_plan	status	pay
0	A	активный	4368.214286
1	A	пассивный	6017.550000
2	B	активный	3432.673913
3	B	пассивный	2518.036301
4	C	активный	2440.674074
5	C	пассивный	1789.830556

	optimal_tariff	status	after_optimisation
0	A	активный	5936.800000
1	A	пассивный	6017.550000
2	B	активный	4238.838462
3	B	пассивный	2518.036301
4	C	активный	2050.711905
5	C	пассивный	1789.830556